package mcia_gui;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;

/**
 * DatabaseEss.java - essentials for making connection with derby database
 *
 * @author MCIA
 */
public final class DatabaseEss {

    public Connection conn = null;                                              //variable for initializing connection
    public Statement stmt = null;                                               //used for making statements

    public static void main(String[] args) {
        //
    }

    DatabaseEss(String dbName) {
        setDBSystemDir(dbName);
        createDBConnection("jdbc:derby:" + dbName + ";create=true;user=admin;password=admin");
    }

    public void setDBSystemDir(String dbName) {
        //localization of db
        String dbDir = System.getProperty("user.dir", ".");
        dbDir = dbDir + "/db_" + dbName;

        //set the db system directory
        System.setProperty("derby.system.home", dbDir);
        System.out.println("Saving db directory as: " + dbDir);
    }

    public void createDBConnection(String connURL) {
        try {
            Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
        } catch (Exception ex) {
            System.out.println("Error1: " + ex);
            JOptionPane.showMessageDialog(Main.container, "ERROR: No library for database connection found! \nImport library for derby database: '/lib/derby.jar'.", "ERROR launching application. Library missing!", JOptionPane.ERROR_MESSAGE);
            System.exit(-1);
        }

        try {
            conn = DriverManager.getConnection(connURL);
        } catch (SQLException ex) {
            System.out.println("Error1a: " + ex);
        }

    }

    public void insertIntoDB(String tableName, String columnName, String record) {
        try {
            stmt = conn.createStatement();
            stmt.execute("insert into " + tableName + "(" + columnName + ") values ('" + record + "')");
            stmt.close();
        } catch (Exception ex) {
            System.out.println("Error2: " + ex);
        }
    }

    public boolean insertDBQuery(String query) {
        boolean status = false;
        try {
            stmt = conn.createStatement();
            stmt.execute(query);
            stmt.close();
            status = true;
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseEss.class.getName()).log(Level.SEVERE, null, ex);
        }
        return status;
    }

    public void selectFromDB(String tableName) {
        try {
            stmt = conn.createStatement();
            ResultSet results = stmt.executeQuery("SELECT * FROM " + tableName);
            ResultSetMetaData rsmd = results.getMetaData();
            int numberCols = rsmd.getColumnCount();
            for (int i = 1; i <= numberCols; i++) {
                //print Column Names
                System.out.print(rsmd.getColumnLabel(i) + "\t\t");
            }

            System.out.println("\n-------------------------------------------------");

            while (results.next()) {
                int id = results.getInt(1);
                String restName = results.getString(2);
                //String cityName = results.getString(3);
                System.out.println(id + "\t\t" + restName);
            }
            results.close();
            stmt.close();
        } catch (Exception ex) {
            System.out.println("Error3: " + ex + " Code: " + ex.getMessage());
        }
    }

    public void shutdownDB(String dbName) {
        try {
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                DriverManager.getConnection("jdbc:derby:" + dbName + ";shutdown=true");
                conn.close();
            }
        } catch (SQLException ex) {
            System.out.println("Database off: " + ex.getMessage());
        }

    }
}
